package bcs.icode.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

public class DBConnection {
	Connection conn;
	String connStr, uid, pwd;
	
	public DBConnection() {
	}
	
	public void setConnection(String connStr, String uid, String pwd) {
		this.connStr = connStr;
		this.uid = uid;
		this.pwd = pwd;
	}
	
	public boolean connect() {
		try {
			if (uid != null && uid.length() > 0)
				conn = DriverManager.getConnection(connStr, uid, pwd);
			else
				conn = DriverManager.getConnection(connStr);
			return true;
		} catch (SQLException ex) {
			return false;
		}
	}
	
	public void close() {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException ex) {
			
		}
		conn = null;
	}
	
	private PreparedStatement prepareStatement(String sql, Object[] params) {
		try {
			PreparedStatement stmt = conn.prepareStatement(sql);
			ParameterMetaData metadata =  stmt.getParameterMetaData();
			
			for (int idx = 0; idx < params.length; idx++) {
				Object v = params[idx];
				if (v == null)
					stmt.setNull(idx + 1, metadata.getParameterType(idx + 1));
				else if (v instanceof String)
					stmt.setString(idx + 1, (String) v);
				else if (v instanceof Integer)
					stmt.setInt(idx + 1, ((Integer)v).intValue());
				else if (v instanceof Double)
					stmt.setDouble(idx + 1, ((Double) v).doubleValue());
				else if (v instanceof Boolean)
					stmt.setBoolean(idx + 1, ((Boolean) v).booleanValue());
				else
					stmt.setString(idx + 1, v.toString());
			}
			
			return stmt;
		} catch (SQLException ex) {
			return null;
		}
	}
	
	public boolean exec(String sql, Object[] params) {
		PreparedStatement stmt = prepareStatement(sql, params);
		if (stmt == null)
			return false;
		try {
			return stmt.execute();
		} catch (SQLException ex) {
			return false;
		}
	}
	
	public DBResultSet select(String sql, Object[] params) {
		PreparedStatement stmt = prepareStatement(sql, params);
		if (stmt == null)
			return null;
		try {
			ResultSet rs = stmt.executeQuery();
			ResultSetMetaData meta = rs.getMetaData();
			int colCount = meta.getColumnCount();
			String[] columns = new String[colCount];
			
			for (int idx = 0; idx < colCount; idx++)
				columns[idx] = meta.getColumnName(idx + 1);
			
			ArrayList<Object[]> rows = new ArrayList<Object[]>();
			while (rs.next()) {
				Object[] row = new Object[colCount];
				for (int idx = 0; idx < colCount; idx++) {
					row[idx] = rs.getObject(idx + 1);
				}
				rows.add(row);
			}
			rs.close();
			DBResultSet results = new DBResultSet();
			results.rows = rows.toArray(new Object[rows.size()][]);
			results.columns = columns;
			return results;
		} catch (SQLException ex) {
			return null;
		}
	}
}
